﻿using PoorMansTSqlFormatterLib;
using SqlServerProfilerTools;
using SqlServerProfilerTools.Data;
using SqlServerProfilerTools.Impl;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Common.Library.Helper
{
    /// <summary>
    /// Sql Server 分析器助手类
    /// </summary>
    public class SqlServerProfilerHelper
    {
        public static void StartSqlServerProfiler()
        {
            /*********************************
             * SqlServerTools是一个第三方类库，具体作者不详,具体原理是模拟Sql Server Profiler 生成 trc 文件跟踪实现
             * 该类库提取是从GitHub https://github.com/ren85/free-sql-server-profiler 的项目上提取到的
             * 根据GitHub的说法，下列代码会在SqlServer数据库实例的DATA目录下生成若干.trc文件 通过Eerverthing搜索证实了该说法
             * SqlServer数据库实例的Data路径可以通过 
             * " use master  select top 1 rtrim([physical_name])   from sys.database_files  where file_id = 1 " sql语句查询得到
             * --查看当前有多少profiler在后台运行
             *   SELECT * FROM sys.fn_trace_getinfo(0)  WHERE property = 2
             *
             *   --停止profiler跟踪
             *   exec sp_trace_setstatus <traceid>, 0
             *   --删除profiler跟踪
             *   exec sp_trace_setstatus <traceid>, 2
             *********************************/
            Profiler profiler = ToolsFactory.Instance.CreateProfiler("127.0.0.1", "sa", "123456", null) as Profiler;
            //profiler.Initialize(TraceOptions.FileRollover, ComputerInfoHelper.GetMachineName() + "-SSPS-"+ DateTime.Now.ToString("yyyy-MM-dd HH.mm.ss") + "-SSPE-",5);
            profiler.Initialize(TraceOptions.FileRollover, ComputerInfoHelper.GetMachineName() + "", 5);
            string s = profiler.TraceFilePath;

            //字段类型大致与Sql Server Profiler 监听到的数据列一致
            var TraceFieldArr = new TraceField[] {
                TraceField.EventClass,
                TraceField.ServerName,
                TraceField.TextData,
                TraceField.StartTime,
                TraceField.Writes,
                TraceField.Reads,
                TraceField.DatabaseID,
                TraceField.ApplicationName
            };

            //profiler.Close();
            //return;

            //事件类别文档MSDN查看：https://docs.microsoft.com/zh-cn/sql/relational-databases/event-classes/tsql-event-category?view=sql-server-ver15
            //添加监听的事件
            TraceEventProperties eventPropSql = new TraceEventProperties()
            {
                Event = TraceEvent.SQLBatchCompleted,
                Fields = TraceFieldArr
            };
            profiler.AddTraceEvent(eventPropSql.Event, eventPropSql.Fields);

            //添加监听的事件
            TraceEventProperties eventPropSP = new TraceEventProperties()
            {
                Event = TraceEvent.RPCCompleted,
                Fields = TraceFieldArr
            };
            profiler.AddTraceEvent(eventPropSP.Event, eventPropSP.Fields);

            //添加监听的事件
            //TraceEventProperties eventPropSqlBatchStarting = new TraceEventProperties()
            //{
            //    Event = TraceEvent.SQLBatchStarting,
            //    Fields = TraceFieldArr
            //};
            //profiler.AddTraceEvent(eventPropSqlBatchStarting.Event, eventPropSqlBatchStarting.Fields);

            //TraceEventProperties eventPropCompleted = new TraceEventProperties()
            //{
            //    Event = TraceEvent.RPCCompleted,
            //    Fields = TraceFieldArr
            //};
            //profiler.AddTraceEvent(eventPropCompleted.Event, eventPropSqlBatchStarting.Fields);


            //过滤条件
            var filter = new FilterProperties(
                   TraceField.ApplicationName,
                   ComparisonOperator.NotEqual,
                   "Report Server");
            if (filter.CheckFilter())
            {
                profiler.AddTraceFilter(filter.Field, LogicalOperator.AND, filter.Operator, filter.TypedValue);
            }
            profiler.TraceEvent += profiler_TraceEvent;
            profiler.Start();
        }
        
        private static void profiler_TraceEvent(object sender, TraceEventArgs e)
        {
            if (e.EventsTable.Rows.Count > 0)
            {
                foreach (DataRow row in e.EventsTable.Rows)
                {
                    string textData = ValueTypeConvertHelper.ToString(row["TextData"]).ToLower();
                    string sql = ValueTypeConvertHelper.ToString(row["TextData"]);
                    //过滤掉@@SPID;查询
                    if (textData.Contains("@@SPID;".ToLower()) || string.IsNullOrEmpty(textData))
                    {
                        continue;
                    }
                    string result = "";
                    foreach (object o in row.ItemArray)
                    {
                        result += " " + o.ToString();
                    }
                    Console.WriteLine(result);
                    if (textData.Contains("sp_executesql".ToLower()))
                    {
                        string sqlFormat = FormateSql(SpExecutesqlConvertoSql(sql));
                        Console.WriteLine(" sqlFormat: \r\n" + sqlFormat);
                    }
                }
            }
        }

        /// <summary>
        /// 格式化Sql 格式化失败返回原SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string FormateSql(string sql)
        {
            try
            {
                //使用Nuget搜索 PoorMansTSQLFormatter 
                //GitHub: https://github.com/TaoK/PoorMansTSqlFormatter                 
                sql = SqlFormattingManager.DefaultFormat(sql);
            }
            catch
            {

            }
            return sql;
        }

        /// <summary>
        /// 将 sp_executesql 语句格式化为普通的可执行Sql语句
        /// <para>转换原理为使用正则表达式进行格式化</para>
        /// <para>格式化失败返回原语句</para>
        /// <para>正则表达式来源：https://www.soinside.com/question/zzKcXLzr9YLoX6T467U2a8 </para>
        /// </summary>
        /// <returns></returns>
        public static string SpExecutesqlConvertoSql(string origSql)
        {
            var re = new Regex(@"exec*\s*sp_executesql\s+N'([\s\S]*)',\s*N'(@[\s\S]*?)',\s*([\s\S]*)", RegexOptions.IgnoreCase); // 1: the sql, 2: the declare, 3: the setting
            var match = re.Match(origSql);
            if (match.Success)
            {
                var sql = match.Groups[1].Value.Replace("''", "'");
                //var declare = match.Groups[2].Value;
                var setting = match.Groups[3].Value + ',';

                // to deal with comma or single quote in variable values, we can use the variable name to split
                //to deal with comma or single quote in variable values, we can use the variable name to split
                var re2 = new Regex(@"@[^',]*?\s*=");
                var variables = re2.Matches(setting).Cast<Match>().Select(m => m.Value).ToArray();
                var values = re2.Split(setting).Where(s => !string.IsNullOrWhiteSpace(s)).Select(m => m.Trim(',').Trim().Trim(';')).ToArray();

                for (int i = variables.Length - 1; i >= 0; i--)
                {
                    sql = Regex.Replace(sql, "(" + variables[i].Replace("=", "") + ")", values[i], RegexOptions.Singleline);
                }
                return sql;
            }

            //return @"Unknown sql query format.";
            return origSql;
        }
    }
}
